---
title: "WellDoneLasagna Dashboard"
output:
flexdashboard::flex_dashboard:
theme:
version: 4
bootswatch: minty
orientation: columns
source_code: embed
---
```{r setup, include=FALSE}
library(flexdashboard)
library(ggplot2)
library(plotly)
library(tidyverse)
library(RMySQL)
library(DBI)
library(tidytext)
library(wordcloud)
```
```{r}
# Connect to a SQLite in-memory database
database <- dbConnect(MySQL(),
dbname = "wdl_database",
host = "localhost",
user = "user",
password = "password",
port = 3306)
dfData <- dbGetQuery(database, "SELECT * FROM discord_messages")
```
```{r}
result_messagespermonth<- dbGetQuery(database, "
SELECT
UserId,
Name,
COUNT(MessageId) AS TotalMessages,
DATE_FORMAT(Timestamp, '%Y-%m') AS Month
FROM
wdl_database.discord_messages
WHERE
Name IN ('theycallmeq', 'jbuwu', 'snozledozle', 'thefyreprophecy', 'joppertje','lykozen','coeus._','coeus7680')
GROUP BY
UserId, Name, DATE_FORMAT(Timestamp, '%Y-%m')
ORDER BY
DATE_FORMAT(Timestamp, '%Y-%m');
")
result_totalmessages<- dbGetQuery(database, "
SELECT COUNT(*) as TotalMessages
FROM wdl_database.discord_messages;
")
result_mostmessages<- dbGetQuery(database, "
SELECT Name, COUNT(distinct Id) as MostMessages
FROM wdl_database.discord_messages
GROUP BY Name
ORDER BY MostMessages DESC
LIMIT 1;
")
result_nitrodistrobutionperusers<- dbGetQuery(database, "
SELECT PremiumType,
GROUP_CONCAT(DISTINCT Name ORDER BY Name SEPARATOR ', ') AS Users,
COUNT(DISTINCT Name) AS UniqueUserCount
FROM wdl_database.discord_messages
WHERE PremiumType <> 'none'
GROUP BY PremiumType;
")
result_totalmessagesperuser<- dbGetQuery(database, "
SELECT
Name,
COUNT(MessageId) AS TotalMessages
FROM
wdl_database.discord_messages
WHERE
Name IN ('theycallmeq', 'jbuwu', 'snozledozle', 'thefyreprophecy', 'joppertje','lykozen','coeus._','coeus7680')
GROUP BY
Name
HAVING
COUNT(MessageId) >= 100;
")
result_leastmessages<- dbGetQuery(database, "
SELECT
CASE
WHEN Name = 'coeus._' OR Name = 'coeus7680' THEN 'coeus'
ELSE Name
END AS NameAlias,
COUNT(DISTINCT Id) as MostMessages
FROM
wdl_database.discord_messages
WHERE
Name IN ('theycallmeq', 'jbuwu', 'snozledozle', 'thefyreprophecy', 'joppertje', 'lykozen', 'coeus._', 'coeus7680')
GROUP BY
NameAlias
ORDER BY
MostMessages ASC
LIMIT 1;
")
word_frequencies <- dfData %>%
unnest_tokens(word, Content) %>% # Split text into words
count(word, sort = TRUE) # Count and sort by frequency
top_words <- head(word_frequencies, 50)
```
Value Boxes {data-width=100}
-------------------------------------
### Total Messages
```{r}
valueBox(result_totalmessages$TotalMessages, caption = "Total Messages", icon = "fa-comments")
```
### Most Messages
```{r}
valueBox(result_mostmessages$Name, caption = "Most Messages", color = "info", icon = "fa-comments")
```
### Least Messages
```{r}
valueBox(result_leastmessages$Name, caption = "Least Messages", color = "danger", icon = "fa-comments")
```
Column {data-width=500 .tabset}
-----------------------------------------------------------------------
### Monthly Messages
```{r}
plot_messagepermonth <- ggplot(result_messagespermonth, aes(x = Month, y = TotalMessages, fill = Name)) +
geom_bar(stat = "identity") +
coord_flip() + # Flips the coordinates for horizontal bars
theme(axis.text.x = element_text(angle = 90, hjust = 1)) + # Rotate x-axis labels if needed
labs(title = "Monthly Messages per User",
x = "Month",
y = "Total Messages")
ggplotly(plot_messagepermonth)
```
### Total messages
```{r}
plot_totalmessagesperuser <- ggplot(result_totalmessagesperuser, aes(x = Name, y = TotalMessages, fill = Name)) +
geom_bar(stat = "identity") +
coord_flip() + # Flips the coordinates for horizontal bars
theme_minimal() +
labs(title = "Total Messages per User",
x = "",
y = "Total Messages") +
theme(axis.text.x = element_text(angle = 45, hjust = 1),
legend.position = "none") # Removes the legend
# Convert to an interactive plotly object
ggplotly(plot_totalmessagesperuser)
```
Column {data-height=500}
-----------------------------------------------------------------------
### Nitro Distrubution
```{r}
plot_ly(result_nitrodistrobutionperusers, labels = ~PremiumType, values = ~UniqueUserCount, type = 'pie',
textinfo = 'label+percent', insidetextorientation = 'radial') %>%
layout(title = 'Distribution of Premium Types Among Users')
```
### Top words
```{r}
# Assuming top_words is already created and contains the top 50 words
plot_topwords <- ggplot(top_words, aes(x = reorder(word, n), y = n, fill = word)) +
geom_bar(stat = "identity") +
coord_flip() + # Flips the coordinates to make the plot horizontal
scale_fill_viridis_d() + # Use the viridis color palette for discrete data
labs(title = "Top 50 Word Frequencies",
x = "Words",
y = "Frequency") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 90, hjust = 1), # Adjust x-axis labels for readability
legend.position = "none") # Hide the legend
# Convert to an interactive plotly object
ggplotly(plot_topwords)
```